unit uAtualizaCM;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Formulario_Padrao_Rel, RDprint, ExtCtrls, StdCtrls, CheckLst,
  ComCtrls, TFlatButtonUnit, Buttons, BmsXPLabel, TFlatGaugeUnit, db, dbtables;

type
  TfrmAtualizaCM = class(TFrmPad_Rel)
    pb: TFlatGauge;
    lbl: TBmsXPLabel;
    p_fase: TRadioGroup;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormActivate(Sender: TObject);
    procedure bimprimirClick(Sender: TObject);
    procedure btncancelarClick(Sender: TObject);
  private
    { Private declarations }
    function CalculaCm(p_Prod: string): Double;

    function UltimoCustoNota(pProd: string; var pQtde: Double): Double;
    function CustoMedio(pro_codigo: string; qtde_p, custo_p, qtde_c, custo_c: double): double;
  public
    { Public declarations }
  end;

var
  frmAtualizaCM: TfrmAtualizaCM;

implementation

uses clsParametros, clsProduto, Dm, Biblio;

var
  Parametros: TParametros;
  Produto: TProduto;

{$R *.dfm}

procedure TfrmAtualizaCM.FormCreate(Sender: TObject);
begin
  inherited;
  Parametros := TParametros.Create;
  Produto := TProduto.Create;

end;

procedure TfrmAtualizaCM.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Parametros.Free;
  Produto.Free;
end;

procedure TfrmAtualizaCM.FormActivate(Sender: TObject);
var
  tq: TQuery;
begin
  inherited;
  tq := TQuery.create(DmInt.db);
  tq.DatabaseName := dmint.db.databasename;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('Select * from FaseProcesso');
  tq.open;
  if FileExists(parametros.Repos_personal + 'uniplacas') then begin
    p_fase.Columns := 6;
    p_fase.Font.Size := 7;
  end;
  p_fase.Items.Clear;
  while not tq.eof do begin
    if not FileExists(parametros.Repos_personal + 'uniplacas') then
      p_fase.Items.Add(tq.fieldbyname('Fase_codigo').AsString + '-' + tq.fieldbyname('Fase_descricao').AsString)
    else
      p_fase.Items.Add(tq.fieldbyname('Fase_codigo').AsString + '-' + copy(tq.fieldbyname('Fase_descricao').AsString, 1, 6));
    tq.next;
  end;
  p_fase.itemindex := 0;
  tq.Close;
  tq.Free;
  Parametros.Recuperar('1');
  pb.Progress := 0;
  lbl.Caption := '';
end;

procedure TfrmAtualizaCM.bimprimirClick(Sender: TObject);
var
  tq, tq1: TQuery;
  xCustoMed,
    xUltCusto,
    xqtde: Double;
begin
  inherited;
  tq := TQuery.Create(self);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq1 := TQuery.Create(self);
  tq1.DatabaseName := DmInt.Db.DatabaseName;

  tq.Close;
  tq.Params.Clear;
  tq.SQL.Clear;
  tq.SQL.Add('Select Pro_Codigo, Pro_Nome, Pro_saldo, Pro_CustoMedio, Pro_UltCusto from Produto where Fase_Codigo =:Fase');
  tq.ParamByName('Fase').AsString := copy(p_fase.Items[p_fase.ItemIndex], 1, 2);
  tq.Open;
  pb.MaxValue := tq.RecordCount;
  tq.First;
  while not tq.Eof do begin
    pb.Progress := pb.Progress + 1;
    lbl.Caption := tq.FieldByName('Pro_Nome').AsString;
    lbl.Refresh;

    xqtde := 0;
    xUltCusto := UltimoCustoNota(tq.FieldByName('Pro_Codigo').AsString, xqtde);

    if xUltCusto <> 0 then begin
      if tq.FieldByName('Pro_CustoMedio').AsFloat <> 0 then
        xCustoMed := CustoMedio(tq.FieldByName('Pro_Codigo').AsString, tq.FieldByName('Pro_Saldo').AsFloat, tq.FieldByName('Pro_CustoMedio').AsFloat, xqtde, xUltCusto)
      else
        xCustoMed := CustoMedio(tq.FieldByName('Pro_Codigo').AsString, tq.FieldByName('Pro_Saldo').AsFloat, xUltCusto, xqtde, xUltCusto);
      ////////////////////
      tq1.Close;
      tq1.SQL.Clear;
      tq1.Params.Clear;
      tq1.SQL.Add('Update Produto set Pro_CustoMedio =:Custo ' +
        'where Pro_Codigo =:Prod');
      tq1.ParamByName('Prod').AsString := tq.FieldByName('Pro_Codigo').AsString;
      tq1.ParamByName('Custo').AsFloat := xCustoMed;
      tq1.ExecSQL;
      ////////////////////
    end;

    tq.Next;
  end;

  {while not tq.Eof do begin
   pb.Progress:= pb.Progress + 1;
   lbl.Caption:= tq.FieldByName('Pro_Nome').AsString;
   lbl.Refresh;
   tq1.Close;
   tq1.SQL.Clear;
   tq1.Params.Clear;
   tq1.SQL.Add('Update Produto set Pro_CustoMedio =:Custo '+
               'where Pro_Codigo =:Prod');
   tq1.ParamByName('Prod').AsString:= tq.FieldByName('Pro_Codigo').AsString;
   tq1.ParamByName('Custo').AsFloat:= CalculaCm(tq.FieldByName('Pro_Codigo').AsString);
   tq1.ExecSQL;
   tq.Next;
  end; }
  ShowMessage('Custo Atualizado!');
  FormActivate(sender);
  tq.Close;
  tq.Free;
  tq1.Close;
  tq1.Free;
end;

function TfrmAtualizaCM.CalculaCm(p_Prod: string): Double;
var
  tq: TQuery;
  i: Integer;
  acumV,
    acumQ: Double;
begin
  tq := TQuery.Create(self);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.Close;
  tq.SQL.Clear;
  tq.Params.Clear;
  tq.SQL.Add('Select ITNC_VALORUNIT, ITNC_Quantidade from ITEMNOTACOMPRA ' +
    'where Pro_Codigo =:Prod ' +
    'order by ITNC_DATAENTRADA desc');
  tq.ParamByName('Prod').AsString := p_Prod;
  tq.Open;
  acumV := 0;
  acumQ := 0;
  i := 0;
  while not tq.Eof do begin
    inc(i);
    if i = 5 then break;
    acumV := acumV + (tq.FieldByName('Itnc_ValorUnit').AsFloat * tq.FieldByName('Itnc_Quantidade').AsFloat);
    acumQ := acumQ + tq.FieldByName('Itnc_Quantidade').AsFloat;
    tq.Next;
  end;
  if i > 0 then
    Result := acumV / acumQ
  else
    Result := 0;
  tq.Close;
  tq.Free;
end;

procedure TfrmAtualizaCM.btncancelarClick(Sender: TObject);
begin
  inherited;
  FormActivate(sender);
end;

function TfrmAtualizaCM.UltimoCustoNota;
var
  tq: TQuery;
begin
  tq := TQuery.Create(self);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.Close;
  tq.SQL.Clear;
  tq.Params.Clear;
  tq.SQL.Add('select sum(Itnc_ValorUnit) as Valor, sum(Itnc_Quantidade)/count(*) as Qtde, ' +
    '       BKP_NOTA, BKP_SERIE, b.ent_codigo,NOTC_ITEM ' +
    '  from itemnotacompra i, notacompra n, BACKUP_NOTACOMPRA b ' +
    ' where ' +
    '      (i.Notac_Numero = n.Notac_Numero and i.Notc_Serie = n.Notc_Serie and i.Ent_Codigo = n.Ent_Codigo) and ' +
    '      (b.Notac_Numero = n.Notac_Numero and b.Notc_Serie = n.Notc_Serie and b.Ent_Codigo = n.Ent_Codigo) and ' +
    '      Pro_Codigo = ' + QuotedStr(pProd) + ' and ' +
    '      Itnc_DataEntrada in ' +
    '                        (Select max(Itnc_DataEntrada) from ItemNotaCompra i, notacompra n ' +
    '                          where (i.Notac_Numero = n.Notac_Numero and i.Notc_Serie = n.Notc_Serie ' +
    '                            and i.Ent_Codigo = n.Ent_Codigo) and Pro_Codigo = ' + QuotedStr(pProd) + ' ' +
    '                        ) ' +
    'group by BKP_NOTA, BKP_SERIE, b.ent_codigo,NOTC_ITEM  order by BKP_NOTA, Valor desc');
  tq.Open;
  if tq.Eof then begin
    pQtde := 0;
    Result := 0;
  end else begin
    pQtde := tq.FieldByName('Qtde').AsFloat;
    Result := tq.FieldByName('Valor').AsFloat;
  end;
  tq.Close;
  tq.Free;
end;

function TfrmAtualizaCM.CustoMedio(pro_codigo: string; qtde_p, custo_p,
  qtde_c, custo_c: double): double;
var
  vlr1, vlr2,
    qtd, resultado: Double;
begin
  qtd := qtde_p + qtde_c;
  if qtd = 0 then qtd := 1;
  if qtde_p = 0 then qtde_p := 1;
  if qtde_c = 0 then qtde_c := 1;
  vlr1 := qtde_p * custo_p;
  vlr2 := qtde_c * custo_c;
  Resultado := (vlr1 + vlr2) / (qtd);
  vlr1 := 0;
  vlr2 := 0;
  qtde_p := 0;
  qtde_c := 0;
  result := resultado;
end;

end.
